package com.fcscs.cservice.dao.hibernate;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.dao.InRoomDao;
import com.fcscs.cservice.domain.ChargeCategory;
import com.fcscs.cservice.domain.InRoomOrder;
import com.fcscs.cservice.domain.InRoomOrderCharge;
import com.fcscs.cservice.domain.InRoomOrderItem;
import com.fcscs.cservice.domain.InRoomOrderItemCharge;
import com.fcscs.cservice.domain.model.InRoomOrderView;
import com.fcscs.cservice.domain.model.OrderView;

@Repository("inRoomDao")
public class InRoomDaoHibernate implements InRoomDao {

	@Autowired
	private ConnectionManager connMgr;

	private static DBLoc RSVDBLoc = new InRoomOrder().getDBLoc();

	private static final String GET_ORDER = " select * from in_room_order_v fv where fv.id = :id ";

	private static final String FIND_V = " select o.*, "
			+ " g.id as guestProfileId, g.first_name as guest_first_name, g.last_name as guest_last_name, "
			+ " ro.room_number as room_no, r.arrival_date_time as arrival_date, r.departure_date_time as departure_date ";

	private static final String FIND_V_CNT = " select count(id) ";

	private static final String FIND_V_FROM = " from in_room_order o ";

	// private static final String FIND_V_CATEGORY =
	// " left join in_room_order_category oc on oc.order_id = o.id ";

	private static final String FIND_V_JOIN = " inner join guest_profile g on g.id = o.guest_profile_id "
			+ " inner join reservation r on r.id = o.reservation_id " + " left join room ro on ro.id = r.room_id "
			+ " where o.active = true and ";

	private static final String FIND_HISTORY = "  guest_profile_id = :profileId ";
	private static final String FIRST_NAME = " and guest_first_name like :first ";
	private static final String LAST_NAME = " and guest_last_name like :last ";
	private static final String STATUS = " and status = :status ";
	private static final String ROOM_NO = " and room_no = :room ";

	private static final String DELIVERY_END = " and delivery_time < :end ";
	private static final String DELIVERY_START = " and delivery_time >= :start ";

	private static final String UPDATE_IN_ROOM_ORDER = " update in_room_order set status = :status where id = :id ";
	
	
	@Override
	public InRoomOrder saveInRoomOrder(InRoomOrder rsv) {
		Session s = connMgr.getCurrentSession(RSVDBLoc);
		s.saveOrUpdate(rsv);
		return rsv;
	}
	
	@Override
	public InRoomOrderCharge saveInRoomOrderCharge(InRoomOrderCharge rsv) {
		Session s = connMgr.getCurrentSession(RSVDBLoc);
		s.saveOrUpdate(rsv);
		return rsv;
	}
	
	@Override
	public InRoomOrderItem saveInRoomOrderItem(InRoomOrderItem rsv) {
		Session s = connMgr.getCurrentSession(RSVDBLoc);
		s.saveOrUpdate(rsv);
		return rsv;
	}
	
	@Override
	public InRoomOrderItemCharge saveInRoomOrderItemCharge(InRoomOrderItemCharge rsv) {
		Session s = connMgr.getCurrentSession(RSVDBLoc);
		s.saveOrUpdate(rsv);
		return rsv;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<InRoomOrderView> findInRoomOrders(long orgId, long propId, Date startTime, Date endTime,
			String firstName, String lastName, String status, Long categoryId, String roomNo, int start, int numResults) {
		String queryStr = buildFindInRoomOrdersQuery(orgId, propId, startTime, endTime, firstName, lastName, status,
				categoryId, roomNo).toString();
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(FIND_V + FIND_V_FROM + FIND_V_JOIN + queryStr);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		if (startTime != null) {
			q.setTimestamp("start", startTime);
		}
		if (endTime != null) {
			q.setTimestamp("end", endTime);
		}
		if (firstName != null && firstName.trim().isEmpty() == false) {
			q.setString("first", "%" + firstName);
		}
		if (lastName != null && lastName.trim().isEmpty() == false) {
			q.setString("last", "%" + lastName);
		}
		if (status != null && status.trim().isEmpty() == false) {
			q.setString("status", status);
		}
		if (roomNo != null && roomNo.trim().isEmpty() == false) {
			q.setString("room", roomNo);
		}
		if (categoryId != null) {
			q.setLong("cat", categoryId);
		}
		q.addEntity(InRoomOrderView.class);
		q.setFirstResult(start);
		q.setMaxResults(numResults);
		return (List<InRoomOrderView>) q.list();
	}

	private StringBuffer buildFindInRoomOrdersQuery(long orgId, long propId, Date startTime, Date endTime,
			String firstName, String lastName, String status, Long categoryId, String roomNo) {
		StringBuffer sb = new StringBuffer();
		sb.append(" and org_id = :orgId ");
		sb.append(" and property_id = :propId ");

		if (startTime != null) {
			sb.append(DELIVERY_START);
		}
		if (endTime != null) {
			sb.append(DELIVERY_END);
		}
		if (firstName != null && firstName.trim().isEmpty() == false) {
			sb.append(FIRST_NAME);
		}
		if (lastName != null && lastName.trim().isEmpty() == false) {
			sb.append(LAST_NAME);
		}
		if (status != null && status.trim().isEmpty() == false) {
			sb.append(STATUS);
		}
		if (roomNo != null && roomNo.trim().isEmpty() == false) {
			sb.append(ROOM_NO);
		}

		return sb;
	}

	@Override
	public int findInRoomOrdersCnt(long orgId, long propId, Date startTime, Date endTime, String firstName,
			String lastName, String status, Long categoryId, String roomNo) {
		String queryStr = buildFindInRoomOrdersQuery(orgId, propId, startTime, endTime, firstName, lastName, status,
				categoryId, roomNo).toString();
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(
				FIND_V_CNT + FIND_V_FROM + FIND_V_JOIN + queryStr);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		if (startTime != null) {
			q.setTimestamp("start", startTime);
		}
		if (endTime != null) {
			q.setTimestamp("end", endTime);
		}
		if (firstName != null && firstName.trim().isEmpty() == false) {
			q.setString("first", "%" + firstName);
		}
		if (lastName != null && lastName.trim().isEmpty() == false) {
			q.setString("last", "%" + lastName);
		}
		if (status != null && status.trim().isEmpty() == false) {
			q.setString("status", status);
		}
		if (roomNo != null && roomNo.trim().isEmpty() == false) {
			q.setString("room", roomNo);
		}
		if (categoryId != null) {
			q.setLong("cat", categoryId);
		}
		return ((Number) q.uniqueResult()).intValue();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<InRoomOrderView> findInRoomOrders(long orgId, long propId, Date startTime, Date endTime, int start,
			int numResults) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(
				FIND_V + FIND_V_FROM + FIND_V_JOIN
						+ buildFindInRoomOrdersQuery(orgId, propId, startTime, endTime).toString());
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		if (startTime != null) {
			q.setTimestamp("start", startTime);
		}
		if (endTime != null) {
			q.setTimestamp("end", endTime);
		}
		q.addEntity(InRoomOrderView.class);
		q.setFirstResult(start);
		q.setMaxResults(numResults);
		return (List<InRoomOrderView>) q.list();
	}

	private StringBuffer buildFindInRoomOrdersQuery(long orgId, long propId, Date startTime, Date endTime) {
		StringBuffer sb = new StringBuffer();
		sb.append(" and org_id = :orgId ");
		sb.append(" and property_id = :propId ");
		if (startTime != null) {
			sb.append(DELIVERY_START);
		}
		if (endTime != null) {
			sb.append(DELIVERY_END);
		}
		return sb;
	}

	@Override
	public int findInRoomOrdersCnt(long orgId, long propId, Date startTime, Date endTime) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(
				FIND_V_CNT + FIND_V_FROM + FIND_V_JOIN
						+ buildFindInRoomOrdersQuery(orgId, propId, startTime, endTime).toString());
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		if (startTime != null) {
			q.setTimestamp("start", startTime);
		}
		if (endTime != null) {
			q.setTimestamp("end", endTime);
		}
		return ((Number) q.uniqueResult()).intValue();
	}

	@Override
	public OrderView getOrderView(long id) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(GET_ORDER);
		q.setLong("id", id);
		q.addEntity(OrderView.class);
		return (OrderView) q.uniqueResult();
	}

	@Override
	public boolean updateInRoomOrder(long id, String status) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(UPDATE_IN_ROOM_ORDER);
		q.setLong("id", id);
		q.setString("status", status);
		return q.executeUpdate() == 1;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<InRoomOrderView> getInRoomOrderHistory(long guestProfileId, int start, int numResults) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(
				FIND_V + FIND_V_FROM + FIND_V_JOIN + FIND_HISTORY);
		q.setLong("profileId", guestProfileId);
		q.addEntity(OrderView.class);
		q.setFirstResult(start);
		q.setMaxResults(numResults);
		return (List<InRoomOrderView>) q.list();
	}

	@Override
	public int getInRoomOrderHistoryCnt(long guestProfileId) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(
				FIND_V_CNT + FIND_V_FROM + FIND_V_JOIN + FIND_HISTORY);
		q.setLong("profileId", guestProfileId);
		return ((Number) q.uniqueResult()).intValue();
	}

	@Override
	public ChargeCategory getChargeCategory(long id) {
		return (ChargeCategory) connMgr.getCurrentSession(RSVDBLoc).load(ChargeCategory.class, id);
	}

	@Override
	public boolean deleteCookOptions(long orderItemId) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(" delete from in_room_order_item_cook where item_id = :item");
		q.setLong("item", orderItemId);
		return q.executeUpdate() >0;
	}

	@Override
	public boolean saveCookOption(long orderItemId, long cookOptionItemId) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(" insert into in_room_order_item_cook(item_id, cook_option_item_id) values(:item, :cook) ");
		q.setLong("item", orderItemId);
		q.setLong("cook", cookOptionItemId);
		return q.executeUpdate() == 1;
	}

	@Override
	public List<Long> getCookOptions(long orderItemId) {
		SQLQuery q = connMgr.getCurrentSession(RSVDBLoc).createSQLQuery(" select cook_option_item_id from in_room_order_item_cook where item_id = :item");
		q.setLong("item", orderItemId);
		q.addScalar("cook_option_item_id", Hibernate.LONG);
		@SuppressWarnings("unchecked")
		List<Object[]> objs = (List<Object[]> )q.list();
		List<Long> ids = new ArrayList<Long>();
		for (Object[] obj : objs) {
			ids.add((Long) obj[0]);
		}
		return ids;
	}

}
